import { BaseModel } from './BaseModel'

export interface Skill {
  id: number
  category_id: number
  name: string
  level: 'beginner' | 'intermediate' | 'advanced' | 'expert'
  color: string
  description?: string
  status: 'active' | 'inactive'
  sort_order: number
  created_at: string
  updated_at: string
}

export interface CreateSkillData {
  category_id: number
  name: string
  level?: 'beginner' | 'intermediate' | 'advanced' | 'expert'
  color?: string
  description?: string
  status?: 'active' | 'inactive'
  sort_order?: number
}

export interface UpdateSkillData {
  category_id?: number
  name?: string
  level?: 'beginner' | 'intermediate' | 'advanced' | 'expert'
  color?: string
  description?: string
  status?: 'active' | 'inactive'
  sort_order?: number
}

export class SkillModel extends BaseModel {
  // 获取所有技能（按分类分组）
  static async getSkillsByCategory(): Promise<{category: any, skills: Skill[]}[]> {
    const [categories] = await this.query(
      'SELECT * FROM skill_categories WHERE status = "active" ORDER BY sort_order ASC, created_at DESC'
    ) as any[]
    const result: {category: any, skills: Skill[]}[] = []

    for (const category of categories) {
      const [skills] = await this.query(
        'SELECT * FROM skills WHERE category_id = ? AND status = "active" ORDER BY sort_order ASC, created_at DESC',
        [category.id]
      ) as [Skill[]]
      result.push({
        category,
        skills: skills as Skill[]
      })
    }

    return result
  }

  // 获取所有技能
  static async getAllSkills(): Promise<Skill[]> {
    const [rows]: any = await this.query(
      'SELECT s.*, sc.name as category_name FROM skills s LEFT JOIN skill_categories sc ON s.category_id = sc.id WHERE s.status = "active" ORDER BY s.sort_order ASC, s.created_at DESC'
    )
    return rows
  }

  // 根据分类ID获取技能
  static async getSkillsByCategoryId(categoryId: number): Promise<Skill[]> {
    const [rows]: any = await this.query(
      'SELECT * FROM skills WHERE category_id = ? AND status = "active" ORDER BY sort_order ASC, created_at DESC',
      [categoryId]
    )
    return rows
  }

  // 创建技能
  static async createSkill(data: CreateSkillData): Promise<number> {
    const result: any = await this.execute(
      `INSERT INTO skills (category_id, name, level, color, description, status, sort_order) 
       VALUES (?, ?, ?, ?, ?, ?, ?)`,
      [
        data.category_id,
        data.name,
        data.level || 'intermediate',
        data.color || '#1890ff',
        data.description || null,
        data.status || 'active',
        data.sort_order || 0
      ]
    )
    return result.insertId
  }

  // 更新技能
  static async updateSkill(id: number, data: UpdateSkillData): Promise<boolean> {
    const fields: string[] = []
    const values: any[] = []

    Object.entries(data).forEach(([key, value]) => {
      if (value !== undefined) {
        fields.push(`${key} = ?`)
        values.push(value)
      }
    })

    if (fields.length === 0) return false

    fields.push('updated_at = CURRENT_TIMESTAMP')
    
    const result: any = await this.execute(
      `UPDATE skills SET ${fields.join(', ')} WHERE id = ?`,
      [...values, id]
    )

    return result.affectedRows > 0
  }

  // 删除技能
  static async deleteSkill(id: number): Promise<boolean> {
    const result: any = await this.execute(
      'DELETE FROM skills WHERE id = ?',
      [id]
    )

    return result.affectedRows > 0
  }
}